package bank.model.dao.mysql;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import bank.model.dao.DepartmentDAO;
import bank.model.pool.DBConnectionPool;
import bank.model.properties.DBTables;
import bank.model.sql.builder.SqlString;
import bank.model.vo.Department;


public class MySqlDepartmentDAO implements DepartmentDAO, DBTables {
	private DBConnectionPool connectionPool = DBConnectionPool.getInstance();
	private SqlString sql = new SqlString();	
	
	public void delete(boolean children, int id) throws Exception {
		sql.clear_All();
		sql.set_Delete();
		sql.set_Table_Name(TBL_DEPARTMENTS);
		if (children) 
			throw new Exception("Deleting department with children is not allowed");
		sql.add_Cond("id", Integer.toString(id));
		
		
		if (!sql.generate())
			throw new Exception("Can't generate sql statement. " + sql.get_last_error());
		Connection con = connectionPool.getConnection();			
		if (con == null)
			throw new Exception("Can't establish connection with DB");
		Statement s = con.prepareStatement(sql.get_Command());
		
		System.out.println( sql.get_Command() );
		
		if (s.executeUpdate(sql.get_Command()) == 0)
			throw new Exception("Can't perform deparment deletion");
	}
	
	public List getBoss(int id) {
		// TODO Auto-generated method stub
		
		return null;
	}
	
	static int off;
	
	public List<Department> getChildren(Integer id) throws Exception {
		List<Department> depts = new ArrayList<Department>(); 
		sql.clear_All();		
		sql.set_Select();
		sql.set_Table_Name(TBL_DEPARTMENTS);
		String pid = (id == null || id == 0) ? "null" : id.toString();		
		sql.add_Cond("PARENT_ID", pid);
		sql.add_Param("ID", "ID");
		sql.add_Param("NAME", "NAME");
		
		if (id == 1) off = 0;
		
		if (!sql.generate())
			throw new Exception("Can't generate sql statement. " + sql.get_last_error());
		Connection con = connectionPool.getConnection();
		if (con == null)
			throw new Exception("Can't establish connection with DB");
		Statement s = con.prepareStatement(sql.get_Command());
		ResultSet resultSet = s.executeQuery(sql.get_Command());
		
		
		while (resultSet.next()) {
			String offset = "";
			 
			for (int i=0; i<off; i++) 
				offset += "&nbsp;&nbsp;";
			offset += '-';
			
			Department dept = new Department(resultSet.getInt("ID"), 
					offset+resultSet.getString("NAME"));			
			depts.add(dept);
			int step = 3;
			off += step;
			depts.addAll(getChildren(dept.getId()));
			off -= step;
		}		
		return depts;
	}

	public List getPayments(int id) {
		// TODO Auto-generated method stub
		return null;
	}

	public List getStaff(int id) {
		// TODO Auto-generated method stub
		return null;
	}

	public void add(String name, int pid) throws Exception {
		Integer parent = (pid == 0) ? null : pid;		
		sql.clear_All();
		sql.set_Insert();
		sql.set_Table_Name(TBL_DEPARTMENTS);
		sql.add_Param("NAME", name);		
		sql.add_Param("PARENT_ID", Integer.toString(parent));		
		sql.generate();
		
		if (!sql.generate())
			throw new Exception("Can't generate sql statement. " + sql.get_last_error());
		Connection con = connectionPool.getConnection();
		if (con == null)
			throw new Exception("Can't establish connection with DB");
		Statement s = con.prepareStatement(sql.get_Command());
		if (s.executeUpdate(sql.get_Command()) != 1)
			throw new Exception("Can't perform deparment insertion");		
	}

	public void update(int id, Department dept) throws Exception {
		// TODO Auto-generated method stub	
		sql.clear_All();
		sql.set_Update();
		sql.set_Table_Name(TBL_DEPARTMENTS);
		sql.add_Cond("ID", Integer.toString(id));
		sql.add_Param("NAME", dept.getName());		
				
		if (!sql.generate())
			throw new Exception("Can't generate sql statement. " + sql.get_last_error());
		Connection con = connectionPool.getConnection();
		if (con == null)
			throw new Exception("Can't establish connection with DB");
		Statement s = con.prepareStatement(sql.get_Command());
		if (s.executeUpdate(sql.get_Command()) != 1)
			throw new Exception("Can't perform deparment update");
		
	}
	
	public Department findById(int id) throws Exception { 
		sql.clear_All();		
		sql.set_Select();
		sql.set_Table_Name(TBL_DEPARTMENTS);
		sql.add_Cond("ID", Integer.toString(id));
		sql.set_all();		
		
		if (!sql.generate())
			throw new Exception("Can't generate sql statement. " + sql.get_last_error());
		Connection con = connectionPool.getConnection();
		
		if (con == null)
			throw new Exception("Can't establish connection with DB");
		Statement s = con.prepareStatement(sql.get_Command());
		ResultSet resultSet = s.executeQuery(sql.get_Command());
		
		Department dept;
		if (resultSet.next()) 
			dept = new Department(resultSet.getInt("ID"), 
					resultSet.getString("NAME"));			
			else throw new Exception("No department found");	
		return dept;
	}
}